package org.lisy.java.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class MysqlJdbc {
	
    public static void main(String[] args) {
    	Connection connection = MysqlDBUtil.getConnection();
    	try {
    		query(connection);
    		
    		insert(connection);
    		
    		update(connection);
    		
    		delete(connection);
    		
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	MysqlDBUtil.closeConnection();
	}

	private static void delete(Connection connection) throws SQLException {
		// 批量删除
		String sql = "delete from account where id = ?";
		PreparedStatement prepareStatement = connection.prepareStatement(sql);
		prepareStatement.setInt(1, 100);
		prepareStatement.addBatch();
		prepareStatement.setInt(1, 101);
		prepareStatement.addBatch();
		prepareStatement.setInt(1, 102);
		prepareStatement.addBatch();
		int[] executeBatch = prepareStatement.executeBatch();
		for (int i = 0; i < executeBatch.length; i++) {
        	System.out.println("bath " + (i+1) + " delete " + executeBatch[i] + " counts data !");
		}
		prepareStatement.close();
		
		query(connection);
	}

	private static void insert(Connection connection) throws SQLException {
		// 批量新增
		String sql = "insert into account (id, name, money) values (?, ?, ?)";
		PreparedStatement prepareStatement = connection.prepareStatement(sql);
		prepareStatement.setString(1, "100");
		prepareStatement.setString(2, "aaa");
		prepareStatement.setString(3, "1000.0");
		prepareStatement.addBatch();
		prepareStatement.setString(1, "101");
		prepareStatement.setString(2, "bbb");
		prepareStatement.setString(3, "1000.0");
		prepareStatement.addBatch();
		prepareStatement.setString(1, "102");
		prepareStatement.setString(2, "ccc");
		prepareStatement.setString(3, "1000.0");
		prepareStatement.addBatch();
		int[] executeBatch = prepareStatement.executeBatch();
		for (int i = 0; i < executeBatch.length; i++) {
        	System.out.println("bath " + (i+1) + " insert " + executeBatch[i] + " counts data !");
		}
		prepareStatement.close();
		
		query(connection);
	}

	private static void update(Connection connection) throws SQLException {
		// 逐条更新
		String sql = "update account set name = ? where id = ? ";
		PreparedStatement prepareStatement = connection.prepareStatement(sql);
		prepareStatement.setObject(1, "aa");
		prepareStatement.setObject(2, "100");
		int executeUpdate = prepareStatement.executeUpdate();
		System.out.println("update " + executeUpdate + " counts data !");
		prepareStatement.close();
		
		query(connection);
		
		// 批量更新
		Statement statement = connection.createStatement();
		sql = "update account set name = 'bb' where id = 101 ";
		statement.addBatch(sql);
        sql = "update account set name = 'cc' where id = 102 ";
        statement.addBatch(sql);
        int[] executeBatch = statement.executeBatch();
        for (int i = 0; i < executeBatch.length; i++) {
        	System.out.println("bath " + (i+1) + " update " + executeBatch[i] + " counts data !");
		}
        statement.close();
        
        query(connection);
	}

	private static void query(Connection connection) throws SQLException {
		System.out.println("====================");
		Statement statement = connection.createStatement();
		String sql = "select * from account";
		ResultSet resultSet = statement.executeQuery(sql);
		ResultSetMetaData data = resultSet.getMetaData();
		// 获得所有列的数目及实际列数
		int columnCount = data.getColumnCount();
		System.out.println("get column count: " + columnCount);
		for (int i = 1; i <= columnCount; i++) {
			// 获得指定列的列名
			System.out.println("get " + i + " columnName:" + data.getColumnName(i));
			// 获得指定列的数据类型
			System.out.println("get " + i + " columnType number:" + data.getColumnType(i));
			// 获得指定列的数据类型名
			System.out.println("get " + i + " columnType name:" + data.getColumnTypeName(i));
			// 所在的 Catalog 名字
			System.out.println("get " + i + " catalogName:" + data.getCatalogName(i));
			// 对应数据类型的类
			System.out.println("get " + i + " columnClassName:" + data.getColumnClassName(i));
			// 在数据库中类型的最大字符个数
			System.out.println("get " + i + " columnDisplaySize:" + data.getColumnDisplaySize(i));
			// 默认的列的标题
			System.out.println("get " + i + " columnLabel:" + data.getColumnLabel(i));
			// 获得列的模式
			System.out.println("get " + i + " schemaName:" + data.getSchemaName(i));
			// 某列类型的精确度(类型的长度)
			System.out.println("get " + i + " precision:" + data.getPrecision(i));
			// 小数点后的位数
			System.out.println("get " + i + " scale:" + data.getScale(i));
			// 获取某列对应的表名
			System.out.println("get " + i + " tableName:" + data.getTableName(i));
			// 是否自动递增
			System.out.println("get " + i + " isAutoInctement:" + data.isAutoIncrement(i));
			// 在数据库中是否为货币型
			System.out.println("get " + i + " isCurrency:" + data.isCurrency(i));
			// 是否为空
			System.out.println("get " + i + " isNullable:" + data.isNullable(i));
			// 是否为只读
			System.out.println("get " + i + " isReadOnly:" + data.isReadOnly(i));
			// 能否出现在 where 中
			System.out.println("get " + i + " isSearchable:" + data.isSearchable(i));
		}
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            double money = resultSet.getDouble("money");
            System.out.println("id:"+id+", name:"+name+", money:" + money);
        }
        statement.close();
        System.out.println("====================");
	}

}

class MysqlDBUtil {
	// 开启批量操作 rewriteBatchedStatements=true
	// 日期替换成NULL值 zeroDateTimeBehavior=convertToNull
	public static final String URL = "jdbc:mysql://localhost:3306/test?zeroDateTimeBehavior=convertToNull&rewriteBatchedStatements=true";
    public static final String USER = "root";
    public static final String PASSWORD = "root";
    
    private static Connection conn = null;
    
    static{
        try {
            // 1.加载驱动程序
			Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. 获得数据库连接
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    /**
     * 获取连接
     */
    public static Connection getConnection(){
    	return conn;
    }
	
	/**
     * 关闭连接
     */
    public static void closeConnection(){
    	if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
    }
}